﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

using XianShang.Data;
using XianShang.Entity;
using XianShang.Common;

namespace XianShang.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {
        /// <summary>
        /// 在IBONPAY主表中插入一條新數據并返回自增ID值
        /// </summary>
        /// <param name="rbInfo"></param>
        /// <returns></returns>
        public long AddNewIBonPay(IBonPayInfo ibonInfo)
        {
            DbParameter[] getPar ={
                                      DbHelper.MakeInParam("@area",(DbType)SqlDbType.TinyInt,3,ibonInfo.Area),
                                     DbHelper.MakeInParam("@amount",(DbType)SqlDbType.Money,int.MaxValue,ibonInfo.Amount),
                                     DbHelper.MakeInParam("@ordernum",(DbType)SqlDbType.NVarChar,50,ibonInfo.OrderNum),
                                     DbHelper.MakeInParam("@payname",(DbType)SqlDbType.NVarChar,50,ibonInfo.Name),
                                     DbHelper.MakeInParam("@contacttype",(DbType)SqlDbType.NVarChar,50,ibonInfo.ContactType),
                                     DbHelper.MakeInParam("@tel",(DbType)SqlDbType.NVarChar,30,ibonInfo.Tel),
                                     DbHelper.MakeInParam("@email",(DbType)SqlDbType.NVarChar,100,ibonInfo.Email),
                                     DbHelper.MakeInParam("@bbsaccount",(DbType)SqlDbType.NVarChar,100,ibonInfo.BbsAccount),
                                     DbHelper.MakeInParam("@content",(DbType)SqlDbType.NVarChar,500,ibonInfo.Content),
                                     DbHelper.MakeInParam("@status",(DbType)SqlDbType.TinyInt,3,ibonInfo.Status),
                                     DbHelper.MakeInParam("@payStatus",(DbType)SqlDbType.TinyInt,3,ibonInfo.PayStatus),
                                     DbHelper.MakeInParam("@ip",(DbType)SqlDbType.NVarChar,50,ibonInfo.IP),
                                     DbHelper.MakeInParam("@time",(DbType)SqlDbType.DateTime,DateTime.Now.ToString().Length,DateTime.Now.ToString())
                                 };

            string commandText = "INSERT INTO [dbo].[XS_IBonPay]";
            commandText += " ([Area],[OrderNum],[Amount],[PayName],[Tel],[ContactType],[Email],[Content],[PayStatus],[bbsAccount],[Status],[IP],[Time])";
            commandText += "VALUES(@area,@ordernum,@amount,@payname,@tel,@contacttype,@email,@content,@paystatus,@bbsaccount,@status,@ip,@time)";
            commandText += ";SELECT @@IDENTITY";

            return Convert.ToInt64(DbHelper.ExecuteScalar(CommandType.Text, commandText, getPar));
        }

        /// <summary>
        /// 新增一條數據到（IBONPAY子表_帳號數量）CardAccount
        /// </summary>
        /// <param name="caInfo"></param>
        /// <returns></returns>
        public int AddNewIBonPayAccount(IBonPayAccountInfo ibonInfo)
        {
            DbParameter[] getPar ={
                                     DbHelper.MakeInParam("@ibonpayid",(DbType)SqlDbType.Int,Int32.MaxValue,ibonInfo.IBonPayID),
                                     DbHelper.MakeInParam("@account",(DbType)SqlDbType.NVarChar,100,ibonInfo.Account),
                                     DbHelper.MakeInParam("@accounttypes",(DbType)SqlDbType.NVarChar,100,ibonInfo.AccountTypes),
                                     DbHelper.MakeInParam("@time",(DbType)SqlDbType.DateTime,DateTime.Now.ToString().Length,DateTime.Now)
                                 };
            string commandText = "INSERT INTO [dbo].[XS_IBonPayAccount] ([IBonPayId],[Account],[Type],[Time])VALUES(@ibonpayid,@account,@accounttypes,@time)";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }

        /// <summary>
        /// 根據訂單號更新支付狀態
        /// </summary>
        /// <param name="ibonInfo"></param>
        /// <returns></returns>
        public int UpDateIBonPayPayNumAndPayStatus(IBonPayInfo ibonInfo)
        {
            DbParameter[] getPar ={
             DbHelper.MakeInParam("@ordernum",(DbType)SqlDbType.NVarChar,50,ibonInfo.OrderNum),
             DbHelper.MakeInParam("@paynum",(DbType)SqlDbType.NVarChar,50,ibonInfo.PayNum),
             DbHelper.MakeInParam("@paystatus",(DbType)SqlDbType.TinyInt,3,ibonInfo.PayStatus),
             DbHelper.MakeInParam("@paytime",(DbType)SqlDbType.DateTime,ibonInfo.PayTime.Length,ibonInfo.PayTime)
                                  };
            string commandText = "UPDATE [dbo].[XS_IBonPay] SET [PayNum] = @paynum,[PayStatus] =@paystatus,[PayTime] =@paytime WHERE [OrderNum] =@ordernum ";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 取得所有內容
        /// </summary>
        /// <returns></returns>
        public IDataReader GetAllIBonPayAndChildTableInfo()
        {
            this.CreateIBonPaySqlFun();

            string commandText = "SELECT DISTINCT";
            commandText += "[dbo].[XS_IBonPay].*,";
            commandText += "[dbo].AddIbonPayAccountClms([dbo].[XS_IBonPay].[id])AS [Account],";
            commandText += "[dbo].AddIbonPayTypeClms([dbo].[XS_IBonPay].[id])AS [Type]";
            commandText += "FROM ";
            commandText += "[dbo].[XS_IBonPay] INNER JOIN [dbo].[XS_IBonPayAccount] ON ";
            commandText += "[dbo].[XS_IBonPay].[ID] = [dbo].[XS_IBonPayAccount].[IBonPayId]";
            commandText += "WHERE [dbo].[XS_IBonPay].[Status]<>4 ";
            commandText += "ORDER BY [dbo].[XS_IBonPay].[Time] DESC";

            return DbHelper.ExecuteReader(CommandType.Text, commandText);
        }
        /// <summary>
        /// 取得所有內容
        /// </summary>
        /// <returns></returns>
        public IDataReader GetAllIBonPayAndChildTableInfo(string area)
        {
            this.CreateIBonPaySqlFun();

            DbParameter getPar = DbHelper.MakeInParam("@area", (DbType)SqlDbType.TinyInt, 3, area);
            string commandText = "SELECT DISTINCT";
            commandText += "[dbo].[XS_IBonPay].*,";
            commandText += "[dbo].AddIbonPayAccountClms([dbo].[XS_IBonPay].[id])AS [Account],";
            commandText += "[dbo].AddIbonPayTypeClms([dbo].[XS_IBonPay].[id])AS [Type]";
            commandText += "FROM ";
            commandText += "[dbo].[XS_IBonPay] INNER JOIN [dbo].[XS_IBonPayAccount] ON ";
            commandText += "[dbo].[XS_IBonPay].[ID] = [dbo].[XS_IBonPayAccount].[IBonPayId]";
            commandText += "WHERE [dbo].[XS_IBonPay].[Status]<>4 AND [dbo].[XS_IBonPay].[AREA]=@area";
            commandText += "ORDER BY [dbo].[XS_IBonPay].[Time] DESC";

            return DbHelper.ExecuteReader(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據相關條件查找IBONPAY繳費信息
        /// </summary>
        /// <param name="name"></param>
        /// <param name="tel"></param>
        /// <param name="gameAccount"></param>
        /// <param name="payOrder"></param>
        /// <returns></returns>
        public DataSet GetIBonPayAndIBonPayAccountInfo(string name, string tel, string gameAccount, string payOrder)
        {
            #region 根據條件處理參數
            List<string> arrCts = new List<string>();
            List<DbParameter> arrPar = new List<DbParameter>();


            if (!Utils.StrIsEmpty(name))
            {
                arrPar.Add(DbHelper.MakeInParam("@payname", (DbType)SqlDbType.NVarChar, 50, name));
                arrCts.Add("[dbo].[XS_IBonPay].[payname]=@payname");
            }
            if (!Utils.StrIsEmpty(tel))
            {
                arrPar.Add(DbHelper.MakeInParam("@tel", (DbType)SqlDbType.NVarChar, 50, tel));
                arrCts.Add("[dbo].[XS_IBonPay].[tel]=@tel");
            }
            if (!Utils.StrIsEmpty(payOrder))
            {
                arrPar.Add(DbHelper.MakeInParam("@paynum", (DbType)SqlDbType.NVarChar, 50, payOrder));
                arrCts.Add("[dbo].[XS_IBonPay].[payNum]=@paynum");
            }
            if (!Utils.StrIsEmpty(gameAccount))
            {
                arrPar.Add(DbHelper.MakeInParam("@account", (DbType)SqlDbType.NVarChar, 100, gameAccount));
                arrCts.Add("([dbo].[XS_IBonPay].[ID] =(SELECT TOP 1 [dbo].[XS_IBonPayAccount].[IBonPayId] FROM [dbo].[XS_IBonPayAccount] WHERE ([dbo].[XS_IBonPayAccount].[Account] = @account)))");
            }
            arrPar.Add(DbHelper.MakeInParam("@status", (DbType)SqlDbType.TinyInt, 3, 4));
            arrCts.Add("[dbo].[XS_IBonPay].[status]<>@status");
            string conditions = null;

            DbParameter[] getPar = new DbParameter[arrCts.Count];
            for (int iCount = 0; iCount < arrCts.Count; ++iCount)
            {
                if (iCount == 0)
                {
                    conditions = "WHERE " + arrCts[iCount];
                }
                else
                {
                    conditions += " AND " + arrCts[iCount];
                }
                getPar[iCount] = arrPar[iCount];
            }

            #endregion

            string commandText = null;
            commandText += "SELECT  [dbo].[XS_IBonPay].*,[dbo].[XS_IBonPayAccount].[Account],[dbo].[XS_IBonPayAccount].[Type]";
            commandText += "FROM  [dbo].[XS_IBonPay] INNER JOIN [dbo].[XS_IBonPayAccount] ON [dbo].[XS_IBonPay].[ID] = [dbo].[XS_IBonPayAccount].[IBonPayId]";
            commandText += string.Format(" {0} ORDER BY [dbo].[XS_IBonPay].[TIME] DESC", conditions);
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar);

        }
        /// <summary>
        /// 根據實體參數和查找方式查找內容
        /// </summary>
        /// <param name="fcs"></param>
        /// <param name="SearchType">查找方式</param>
        /// <returns></returns>
        public IDataReader GetIBonPayAndChildTableInfo(FullIBonPayInfo fip, int SearchType)
        {
            this.CreateIBonPaySqlFun();

            #region 根據條件處理參數
            List<string> arrCts = new List<string>();
            List<DbParameter> arrPar = new List<DbParameter>();

            if (!Utils.StrIsEmpty(fip.OrderNum))
            {
                arrPar.Add(DbHelper.MakeInParam("@OrderNum", (DbType)SqlDbType.NVarChar, 100, fip.OrderNum));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_IBonPay].[OrderNum]=@OrderNum" : "[dbo].[XS_IBonPay].[OrderNum] LIKE '%'+@OrderNum+'%'");
            }
            if (!Utils.StrIsEmpty(fip.Name))
            {
                arrPar.Add(DbHelper.MakeInParam("@payname", (DbType)SqlDbType.NVarChar, 50, fip.Name));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_IBonPay].[payname]=@payname" : "[dbo].[XS_IBonPay].[payname] LIKE '%'+@payname+'%'");
            }
            if (!Utils.StrIsEmpty(fip.Tel))
            {
                arrPar.Add(DbHelper.MakeInParam("@tel", (DbType)SqlDbType.NVarChar, 50, fip.Tel));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_IBonPay].[tel]=@tel" : "[dbo].[XS_IBonPay].[tel] LIKE '%'+@tel+'%'");
            }
            if (!Utils.StrIsEmpty(fip.Account))
            {
                arrPar.Add(DbHelper.MakeInParam("@account", (DbType)SqlDbType.NVarChar, 100, fip.Account));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_IBonPayAccount].[account]=@account" : "[dbo].[XS_IBonPayAccount].[account] LIKE '%'+@account+'%'");
            }
            if (!Utils.StrIsEmpty(fip.BbsAccount))
            {
                arrPar.Add(DbHelper.MakeInParam("@bbsaccount", (DbType)SqlDbType.NVarChar, 50, fip.BbsAccount));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_IBonPay].[bbsaccount]=@bbsaccount" : "[dbo].[XS_IBonPay].[bbsaccount] LIKE '%'+@bbsaccount+'%'");
            }
            if (!Utils.StrIsEmpty(fip.IP))
            {
                arrPar.Add(DbHelper.MakeInParam("@ip", (DbType)SqlDbType.NVarChar, 50, fip.IP));
                arrCts.Add(SearchType == 1 ? "[dbo].[XS_IBonPay].[ip]=@ip" : "[dbo].[XS_IBonPay].[ip] LIKE '%'+@ip+'%'");
            }
            if (!Utils.StrIsEmpty(fip.Time))
            {
                arrPar.Add(DbHelper.MakeInParam("@time", (DbType)SqlDbType.DateTime, 50, fip.Time));
                arrCts.Add(SearchType == 1 ? "DateDiff(DAY,[dbo].[XS_IBonPay].[time],@time)=0" : "YEAR([dbo].[XS_IBonPay].[time])=year(@time) AND MONTH([dbo].[XS_IBonPay].[time])=MONTH(@time) AND DAY([dbo].[XS_IBonPay].[time])=DAY(@time)");
            }
            if (!Utils.StrIsEmpty(fip.Area))
            {
                arrPar.Add(DbHelper.MakeInParam("@area", (DbType)SqlDbType.TinyInt, 3, fip.Area));
                arrCts.Add("[dbo].[XS_IBonPay].[area]=@area");
            }
            if (fip.Status >= 0)
            {
                arrPar.Add(DbHelper.MakeInParam("@status", (DbType)SqlDbType.TinyInt, 3, fip.Status));
                arrCts.Add("[dbo].[XS_IBonPay].[status]=@status");

            }
            if (fip.PayStatus > 0)
            {
                arrPar.Add(DbHelper.MakeInParam("@paystatus", (DbType)SqlDbType.TinyInt, 3, fip.PayStatus));
                arrCts.Add("[dbo].[XS_IBonPay].[paystatus]=@paystatus");
            }
            string conditions = null;

            DbParameter[] getPar = new DbParameter[arrPar.Count];
            for (int iCount = 0; iCount < arrCts.Count; ++iCount)
            {
                if (iCount == 0)
                {
                    conditions += " WHERE " + arrCts[iCount];
                }
                else
                {
                    conditions += " AND " + arrCts[iCount];
                }
                getPar[iCount] = arrPar[iCount];
            }
            #endregion

            string commandText = "SELECT DISTINCT";
            commandText += "[dbo].[XS_IBonPay].*,";
            commandText += "[dbo].AddIbonPayAccountClms([dbo].[XS_IBonPay].[id])AS [Account],";
            commandText += "[dbo].AddIbonPayTypeClms([dbo].[XS_IBonPay].[id])AS [Type]";
            commandText += "FROM ";
            commandText += "[dbo].[XS_IBonPay] INNER JOIN [dbo].[XS_IBonPayAccount] ON ";
            commandText += "[dbo].[XS_IBonPay].[ID] = [dbo].[XS_IBonPayAccount].[IBonPayId]";
            commandText += string.Format(" {0} ", conditions);
            commandText += "ORDER BY [dbo].[XS_IBonPay].[Time] DESC";

            return DbHelper.ExecuteReader(CommandType.Text, commandText, getPar);
        }

        /// <summary>
        /// 更新Message ProcessIp Status ProcessTime四個字段
        /// </summary>
        /// <param name="cs"></param>
        /// <returns></returns>
        public int UpIBonPayMessageProcessIpStatusProcessTime(FullIBonPayInfo fip)
        {
            DbParameter[] getPar ={
                                     DbHelper.MakeInParam("@id",(DbType)SqlDbType.Int,int.MaxValue,fip.ID),
                                     DbHelper.MakeInParam("@message",(DbType)SqlDbType.NVarChar,500,fip.Message),
                                     DbHelper.MakeInParam("@processip",(DbType)SqlDbType.NVarChar,50,fip.ProcessIP),
                                     DbHelper.MakeInParam("@processtime",(DbType)SqlDbType.DateTime,fip.ProcessTime.Length,fip.ProcessTime),
                                     DbHelper.MakeInParam("@status",(DbType)SqlDbType.TinyInt,4,fip.Status)
                                 };
            string commandText = "UPDATE [dbo].[XS_IBonPay]";
            commandText += "SET [Message] = @message,[Status] = @status,[ProcessIP] = @processip,[ProcessTime] = @processtime ";
            commandText += "WHERE [dbo].[XS_IBonPay].[id]=@id";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 更新 ProcessIp Status ProcessTime三個字段
        /// </summary>
        /// <param name="cs"></param>
        /// <returns></returns>
        public int UpIBonPayStatusProcessTimeProcessIpById(FullIBonPayInfo fip)
        {
            DbParameter[] getPar ={
                                     DbHelper.MakeInParam("@id",(DbType)SqlDbType.Int,int.MaxValue,fip.ID),
                                     DbHelper.MakeInParam("@processip",(DbType)SqlDbType.NVarChar,50,fip.ProcessIP),
                                     DbHelper.MakeInParam("@processtime",(DbType)SqlDbType.DateTime,fip.ProcessTime.Length,fip.ProcessTime),
                                     DbHelper.MakeInParam("@status",(DbType)SqlDbType.TinyInt,4,fip.Status)
                                 };
            string commandText = "UPDATE [dbo].[XS_IBonPay]";
            commandText += "SET [Status] = @status,[ProcessIP] = @processip,[ProcessTime] = @processtime ";
            commandText += "WHERE [dbo].[XS_IBonPay].[id]=@id";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據ID查找記錄
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable GetIBonPayById(string id)
        {
            this.CreateIBonPaySqlFun();

            DbParameter getPar = DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, int.MaxValue, id);
            string commandText = "SELECT DISTINCT";
            commandText += "[dbo].[XS_IBonPay].*,";
            commandText += "[dbo].AddIbonPayAccountClms([dbo].[XS_IBonPay].[id])AS [Account],";
            commandText += "[dbo].AddIbonPayTypeClms([dbo].[XS_IBonPay].[id])AS [Type]";
            commandText += "FROM ";
            commandText += "[dbo].[XS_IBonPay] INNER JOIN [dbo].[XS_IBonPayAccount] ON ";
            commandText += "[dbo].[XS_IBonPay].[ID] = [dbo].[XS_IBonPayAccount].[IBonPayId]";
            commandText += " WHERE [dbo].[XS_IBonPay].[ID]=@id ";
            commandText += "ORDER BY [dbo].[XS_IBonPay].[Time] DESC";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar).Tables[0];
        }
        /// <summary>
        /// 根據ID刪除記錄
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelIBonPayById(string id)
        {
            DbParameter getPar = DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, int.MaxValue, id);
            string commandText = "DELETE FROM [dbo].[XS_IBonPay] WHERE [dbo].[XS_IBonPay].[ID]=@id";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據ID刪除記錄
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelIBonPayAccountByIBonPayId(string id)
        {
            DbParameter getPar = DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, int.MaxValue, id);
            string commandText = "DELETE FROM [dbo].[XS_IBonPayAccount] WHERE [dbo].[XS_IBonPayAccount].[ibonpayid]=@id";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }



        #region 私有函數
        /// <summary>
        /// 創建SQL函數
        /// </summary>
        private void CreateIBonPaySqlFun()
        {
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append("SET ANSI_NULLS ON\r\n");
            sb.Append("GO\r\n");
            sb.Append("SET QUOTED_IDENTIFIER ON\r\n");
            sb.Append("GO\r\n");
            sb.Append("IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[AddIbonPayTypeClms]') AND xtype in (N'FN', N'IF', N'TF'))\r\n");
            sb.Append("BEGIN\r\n");
            sb.Append("execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[AddIbonPayTypeClms](@id nvarchar(10))\r\n");
            sb.Append("returns nvarchar(4000)\r\n");
            sb.Append("AS\r\n");
            sb.Append("BEGIN\r\n");
            sb.Append("declare @str varchar(5000)\r\n");
            sb.Append("select @str = isnull(@str + '','' , '''') +Type  from XS_IBonPay left join XS_IBonPayAccount on XS_IBonPay.id=XS_IBonPayAccount.IBonPayId where XS_IBonPay.id = @id\r\n");
            sb.Append("return @str\r\n");
            sb.Append("END\r\n");
            sb.Append("'\r\n");
            sb.Append("END\r\n");
            sb.Append("GO\r\n");
            sb.Append("SET ANSI_NULLS ON\r\n");
            sb.Append("GO\r\n");
            sb.Append("SET QUOTED_IDENTIFIER ON\r\n");
            sb.Append("GO\r\n");
            sb.Append("IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[AddIbonPayAccountClms]') AND xtype in (N'FN', N'IF', N'TF'))\r\n");
            sb.Append("BEGIN\r\n");
            sb.Append("execute dbo.sp_executesql @statement = N'\r\n");
            sb.Append("CREATE FUNCTION [dbo].[AddIbonPayAccountClms](@id nvarchar(10))\r\n");
            sb.Append("returns nvarchar(4000)\r\n");
            sb.Append("AS\r\n");
            sb.Append("BEGIN\r\n");
            sb.Append("declare @str varchar(5000)\r\n");
            sb.Append("select @str = isnull(@str + '','' , '''') +Account  from XS_IBonPay left join XS_IBonPayAccount on XS_IBonPay.id=XS_IBonPayAccount.IBonPayId where XS_IBonPay.id = @id\r\n");
            sb.Append("return @str\r\n");
            sb.Append("END\r\n");
            sb.Append("'\r\n");
            sb.Append("END\r\n");
            sb.Append("GO\r\n");
            DbHelper.ExecuteCommandWithSplitter(sb.ToString());
        }
        #endregion
    }
}
